// 获取参数
var startTime = params.startTime;
var endTime = params.endTime;
// 销售区域
var bnbmRegion = params.bnbmRegion;
// 区域层级
var level = params.level;

// 根据统计层级改变bnbm_region信息，例如：华东_上海区_上海，统计为1级的时候，将所有的都改为华东
if (Number(level) > 0 && Number(level) < 4) {
    var regionArr = bnbmRegion.split("_");
    var size = regionArr.length > level ? level : regionArr.length;
    var region = regionArr[0];
    for (var d = 1; d < level; d++) {
        region = region + "_" + regionArr[d];
    }
    bnbmRegion = region;
}

print("bnbmRegion: " + bnbmRegion);

Date.prototype.Format = function (fmt) { //author: meizz
    var o = {
        "M+": this.getMonth() + 1,                 //月份
        "d+": this.getDate(),                    //日
        "h+": this.getHours(),                   //小时
        "m+": this.getMinutes(),                 //分
        "s+": this.getSeconds(),                 //秒
        "q+": Math.floor((this.getMonth() + 3) / 3), //季度
        "S": this.getMilliseconds()             //毫秒
    };
    if (/(y+)/.test(fmt))
        fmt = fmt.replace(RegExp.$1, (this.getFullYear() + "").substr(4 - RegExp.$1.length));
    for (var k in o)
        if (new RegExp("(" + k + ")").test(fmt))
            fmt = fmt.replace(RegExp.$1, (RegExp.$1.length == 1) ? (o[k]) : (("00" + o[k]).substr(("" + o[k]).length)));
    return fmt;
}


startTime = new Date(startTime).Format("yyyy-MM-dd hh:mm:ss");
endTime = new Date(endTime).Format("yyyy-MM-dd hh:mm:ss");
// 链接数据库k3的
var host = 'jdbc:sqlserver://103.235.238.21:1433;DatabaseName=k3cloud;';
var name = 'cloudsys';
var pass = '@163.com';
var fac = core.getJdbcDataSource('sqlserver', host, name, pass);
var sql = "";
sql += "SELECT a.*, CASE WHEN b.jtclient_name IS NOT NULL THEN b.jtclient_name ELSE b.client_name END a4 , b.client_name clientName                     ";
sql += "FROM (SELECT                                                                                                                                    ";
sql += "bs.saleorg_name a1,                                                                                                                             ";
if (bnbmRegion) {
    sql += "    left(bs.bnbm_region,len('" + bnbmRegion + "')) a2,                                                                                      ";
} else {
    sql += "    bs.bnbm_region a2,                                                                                      ";
}
sql += "    bs.client_number a3,                                                                                                                        ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a5,                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a6,                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '北新' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a7,                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '北新' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a8,                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '北新' AND bs.type2 != '龙牌' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a9,     ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '北新' AND bs.type2 != '龙牌' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a10, ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a11,                                                ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a12,                                             ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a13,                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a14,                       ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a15,                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a16,                      ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a17,                                              ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a18,                                           ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '装饰石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a19,                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '装饰石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a20,                                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '粉料' THEN bs.ex_unit_qty ELSE 0 END)) a21,                                                 ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '粉料' THEN bs.row_amount_rmb ELSE 0 END)) a22,                                              ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '矿棉板' THEN bs.ex_unit_qty ELSE 0 END)) a29,                                               ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '矿棉板' THEN bs.row_amount_rmb ELSE 0 END)) a30,                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '烤漆龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a31,                                             ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '烤漆龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a32,                                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '外采主产品配套件' THEN bs.row_amount_rmb ELSE 0 END)) a37,                                    ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 != '烤漆龙骨' THEN bs.row_amount_rmb ELSE bs.row_amount_rmb END)) a38                          ";
sql += "FROM bi_saleorder bs                                                                                                                           ";
sql += "WHERE  bs.billdate between ' " + startTime + " ' and '" + endTime + " ' ";
if (bnbmRegion) {
    sql += " AND bs.bnbm_region LIKE '" + bnbmRegion + "%' ";
    sql += "GROUP BY bs.saleorg_name,left(bs.bnbm_region,len('" + bnbmRegion + "')),bs.client_number                                                   ";
} else {
    sql += "GROUP BY bs.saleorg_name,bs.bnbm_region,bs.client_number                                                  ";
}
sql += "UNION ALL                                                                                                                                  ";
sql += "SELECT                                                                                                                                     ";
sql += "'' a1,                                                                                                                                     ";
if (bnbmRegion) {
    sql += "    left(bs.bnbm_region,len('" + bnbmRegion + "')) a2,                                                                                      ";
} else {
    sql += "    bs.bnbm_region a2,                                                                                      ";
}
sql += "    '小计' a3,                                                                                                                               ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a5,                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a6,                       ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '北新' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a7,                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '北新' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a8,                       ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '北新' AND bs.type2 != '龙牌' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a9,    ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '北新' AND bs.type2 != '龙牌' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a10,";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a11,                                             ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a12,                                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a13,                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a14,                      ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a15,                        ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a16,                     ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a17,                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a18,                                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '装饰石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a19,                                           ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '装饰石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a20,                                        ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '粉料' THEN bs.ex_unit_qty ELSE 0 END)) a21,                                              ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '粉料' THEN bs.row_amount_rmb ELSE 0 END)) a22,                                           ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '矿棉板' THEN bs.ex_unit_qty ELSE 0 END)) a29,                                             ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '矿棉板' THEN bs.row_amount_rmb ELSE 0 END)) a30,                                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '烤漆龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a31,                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '烤漆龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a32,                                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '外采主产品配套件' THEN bs.row_amount_rmb ELSE 0 END)) a37,                                     ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 != '烤漆龙骨' THEN bs.row_amount_rmb ELSE bs.row_amount_rmb END)) a38                         ";
sql += "FROM bi_saleorder bs                                                                                                                       ";
sql += "WHERE  bs.billdate between ' " + startTime + " ' and '" + endTime + " ' ";
if (bnbmRegion) {
    sql += " AND bs.bnbm_region LIKE '" + bnbmRegion + "%' ";
    sql += "GROUP BY left(bs.bnbm_region,len('" + bnbmRegion + "'))                                                                                 ";
} else {
    sql += "GROUP BY bs.bnbm_region                                                                                                                  ";
}
sql += "UNION ALL                                                                                                                                  ";
sql += "SELECT                                                                                                                                     ";
sql += "'' a1,                                                                                                                                     ";
sql += "    CASE WHEN charindex('_',bs.bnbm_region,4) != 0 THEN substring(bs.bnbm_region,0,charindex('_',bs.bnbm_region,4)) ELSE bs.bnbm_region END a2,  ";
sql += "    '业务部小计' a3,                                                                                                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a5,                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a6,                       ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '北新' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a7,                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '北新' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a8,                       ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '北新' AND bs.type2 != '龙牌' AND bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a9,    ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '北新' AND bs.type2 != '龙牌' AND bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a10,";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a11,                                             ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a12,                                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a13,                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 = '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a14,                      ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a15,                        ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type2 != '龙牌'AND bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a16,                     ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '轻钢龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a17,                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '轻钢龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a18,                                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '装饰石膏板' THEN bs.ex_unit_qty ELSE 0 END)) a19,                                           ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '装饰石膏板' THEN bs.row_amount_rmb ELSE 0 END)) a20,                                        ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '粉料' THEN bs.ex_unit_qty ELSE 0 END)) a21,                                              ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '粉料' THEN bs.row_amount_rmb ELSE 0 END)) a22,                                           ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '矿棉板' THEN bs.ex_unit_qty ELSE 0 END)) a29,                                             ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '矿棉板' THEN bs.row_amount_rmb ELSE 0 END)) a30,                                          ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '烤漆龙骨' THEN bs.ex_unit_qty ELSE 0 END)) a31,                                            ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '烤漆龙骨' THEN bs.row_amount_rmb ELSE 0 END)) a32,                                         ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 = '外采主产品配套件' THEN bs.row_amount_rmb ELSE 0 END)) a37,                                     ";
sql += "    Convert(decimal(18,2),SUM(CASE WHEN bs.type1 != '烤漆龙骨' THEN bs.row_amount_rmb ELSE bs.row_amount_rmb END)) a38                         ";
sql += "FROM bi_saleorder bs                                                                                                                       ";
sql += "WHERE  bs.billdate between ' " + startTime + " ' and '" + endTime + " ' ";
if (level) {
    sql += " AND bs.bnbm_region LIKE '" + level + "%' ";
}
sql += "GROUP BY CASE WHEN charindex('_',bs.bnbm_region,4) != 0 THEN substring(bs.bnbm_region,0,charindex('_',bs.bnbm_region,4)) ELSE bs.bnbm_region END  ";
sql += ") a left join bi_client b on a.a3 = b.client_number                                                                                        ";
sql += "WHERE a.a2 IS NOT NULL                                                                                                                     ";
sql += " ORDER BY a.a2,a.a3                                                                                                                         ";
var list = core.jdbcQuery(fac, sql);


// 这里需要添加边遍历边添加的逻辑，将 小计、业务部小计、事业部小计添加进去


list;

